package com.nb.serv;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

public class BaseDAO {
	public JSONObject executeQuery(String sql,int pn,int ps)
			throws Exception {
		Statement stmt = null;
		Connection con = null;
		JSONObject result = new JSONObject();
		ResultSet rs = null;
		try {
			long t1 = System.currentTimeMillis();
			con = JDBCManager.getInstance().getConnetion();
			long t2 = System.currentTimeMillis();
			System.out.println("获取连接时间:"+(t2-t1)+"ms");
			if (null == con)
				throw new Exception("没有获取到数据库连接获!");
			stmt = con.createStatement();
			// 设置查询的最大时间，如果超过这个时间测抛出异常
			stmt.setQueryTimeout(600);
			//sql1 查询总数，sql2查询数据
			String sql1 = "select count(*) from ("+sql+") a";
			int startIndex = (pn-1)*ps+1;
			int endIndex = pn*ps;
			String sql2 = "select * from ("+sql+") a limit "+startIndex+" , "+endIndex;
			
			rs = stmt.executeQuery(sql1);
			int total = 0;
			if(rs.next()){
				total = rs.getInt(1);
			}
			long t3 = System.currentTimeMillis();
			System.out.println("查询数据时间:"+(t3-t2)+"ms");
			
			rs = stmt.executeQuery(sql2);
			// 将查询出来的数据进行转换
			JSONArray rows = new JSONArray();
			long t4 = System.currentTimeMillis();
			System.out.println("查询数据时间:"+(t4-t3)+"ms");
			while(rs.next()){
				ResultSetMetaData rsm = rs.getMetaData();
				JSONObject row  = new JSONObject();
				int colNum = 0;
				colNum = rsm.getColumnCount();
				for(int i = 1;i <= colNum;i++)
				{
					String name = rsm.getColumnName(i);
					Object value = rs.getObject(i);
					row.put(name, value);
				}
				rows.add(row);
			}
			result.put("total", total);
			result.put("rows", rows);
			System.out.println(result.toString());
		} catch (Exception e) {
			throw e;
		} finally {
			if (stmt != null) {
				stmt.close();
				con.close();
			}

		}
		return result;
	}
}
